package entity;

import dao.BaseDao;
import net.sf.json.JSONObject;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 * Admins
 *
 * @author 谭文斌
 * @since 2023/9/4
 */
public class Admins {
    /**
     * 获取图书列表
     * @param page
     * @param limit
     * @return String json字符串
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public Map getBookList(String page, String limit, Map where) throws ClassNotFoundException, SQLException {
        Map<String, Object> map = new HashMap<String, Object>();
        String whereString = "";
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        connection = BaseDao.getConnection();
        int number = Integer.valueOf(page);
        int size = Integer.valueOf(limit);
        String sql = "select * from books ";
        if(where!=null && !where.isEmpty()) {
            whereString += " where "+where.get("condition") +" like '%" +where.get("conditionValue") +"%' ";
            sql += whereString;
        }
        sql += "order by id desc limit ?,? ";
        pstmt = (PreparedStatement) connection.prepareStatement(sql);
        pstmt.setInt(1, (number-1) * size );
        pstmt.setInt(2, size);

        resultSet = pstmt.executeQuery();
        JSONObject json = new JSONObject();
        String result = "";
        int i = 1;

        // 获取行数据
        while( resultSet.next() ) {
            //System.out.println("????-------" +resultSet.getInt("count"));
            json.put("id", resultSet.getInt("id"));
            json.put("name", resultSet.getString("name"));
            json.put("author", resultSet.getString("author"));
            json.put("library_id", resultSet.getInt("library_id"));
            json.put("sort_id", resultSet.getInt("sort_id"));
            json.put("position", resultSet.getString("position"));
            json.put("status", resultSet.getInt("status"));
            json.put("description", resultSet.getString("description"));
            if(i==1) {
                result = json.toString();
            }else {
                result += "," +json.toString();
            }
            i++;
        }
        map.put("data", result);

        // 获取总数count，重写sql
        int count = 0;
        sql = "select count(*) as count  from books ";
        if(where!=null && !where.isEmpty()) {
            sql += whereString;
        }
        pstmt = connection.prepareStatement(sql);
        resultSet = pstmt.executeQuery();
        if(resultSet.next()) {
            count = resultSet.getInt("count");
        }
        map.put("count", count);
        BaseDao.closeAll(resultSet, pstmt, connection);
        return map;

    }

    /**
     * 通过id(String)获取分类名称
     * @param id
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static String getSortName(String id) throws ClassNotFoundException, SQLException {
        if(id==null || id.equals(""))
            return "参数错误";
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        String sql = null;
        String result = null;
        connection =  BaseDao.getConnection();
        sql = "select name from book_sort where id=?";
        pstmt = connection.prepareStatement(sql);
        pstmt.setString(1, id);
        resultSet = pstmt.executeQuery();
        if(resultSet.next()) {
            result = resultSet.getString("name");
        }else {
            result = "查询失败";
        }
        BaseDao.closeAll(null, pstmt, connection);
        return result;
    }

    /**
     * 根据id获取书籍信息
     * @param id
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public ResultSet getBookInfo(int id) throws ClassNotFoundException, SQLException {
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        String sql = "select * from books where id=? ";
        connection = BaseDao.getConnection();
        pstmt = connection.prepareStatement(sql);
        pstmt.setInt(1, id);
        resultSet = pstmt.executeQuery();
        //Base.closeResource(null, null, null);
        connection.close();
        if(resultSet.next()) {
            return resultSet;
        }

        return null;
    }
}
